﻿//------------------------------------------------------------------------------
// <copyright file="ExcelDocument.cs" company="David Martos">
//     Copyright (c) David Martos.  All rights reserved.
// </copyright>
// <summary>The Export to Excel Document class.</summary>
//  ----------------------------------------------------------------------------
namespace Dissemination.MVC.Controllers.Extensions
{
    #region Using statements

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.IO.Packaging;
    using System.Linq;
    using System.Data;
    using OfficeOpenXml;
    using DocumentFormat.OpenXml.Extensions;

    #endregion

    /// <summary>
    /// Excel document class.
    /// </summary>
    public static class ExcelDocument
    {
        /// <summary>
        /// Updates an existing Excel template with the given list of changes in rowData
        /// </summary>
        /// <param name="templateName">Name of the template file</param>
        /// <param name="documentName">Name or the temporal file</param>
        /// <param name="excelWorkSheetName">Name of the sheet</param>
        /// <param name="rowData">Changes in the sheet</param>
        /// <returns>MemoryStream to flush to the user</returns>
        public static MemoryStream Update(string templateName, string documentName, string excelWorkSheetName, IQueryable rowData)
        {
            FileInfo newFile = new FileInfo(documentName);
            FileInfo template = new FileInfo(templateName);
            using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
            {
                ExcelWorksheet baseW = xlPackage.Workbook.Worksheets[excelWorkSheetName];

                foreach (ExcelSheetInformation info in rowData)
                {
                    ExcelWorksheet ws = xlPackage.Workbook.Worksheets[info.Name];
                    if (ws != null)
                    {
                        foreach (ExcelCellChange change in info.Changes)
                        {
                            ws.Cells[change.Row, change.Column].Value = change.Value;
                        }
                    }
                    else
                    {
                        ExcelWorksheet nws = xlPackage.Workbook.Worksheets.Copy(info.Template, info.Name);

                        ExcelWorksheet w = xlPackage.Workbook.Worksheets[info.Template];
                        w.Hidden = eWorkSheetHidden.VeryHidden;

                        foreach (ExcelCellChange change in info.Changes)
                        {
                            nws.Cells[change.Row, change.Column].Value = change.Value;
                        }
                    }
                }

                xlPackage.Save();
            }

            MemoryStream ms = AbstractReader.StreamFromFile(documentName);
            System.IO.File.Delete(documentName);
            return ms;
        }
    }
}